<?php
class ReportsController extends AppController{

     var $name='Reports';
     var $uses = array("CompanyPatent","Constant","Codetype","ProCvalue","Company","Techstack","Talstack",
                        "CompReport","Codevalue","SearchCompany","CompTalent","CompTechnology","CompCarrierlease","Project","CompEconomic","Group");     
     var $helpers=array('Pagination','Html','Javascript','Form','Str','Action','Accp');
     var $components = array ('Pagination');
     var $layout='hindex';
    //首页内容
    function index($sortBy="modified",$direction="desc",$report=null) 
    {
        $this->Session->delete('companyid');
        $this->Session->delete('companyname');
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $industrys=$this->Codetype->getCValues($this->Constant->Industry,"all");
        $this->set(compact('industrys'));
        $pagesize=20;
        if($report){
        	$pagesize=1000;
        }
        $paging['show']=$pagesize;
        $paging['url'] = '/reports/index/'.$sortBy.'/'.$direction;
        $urlcount=0;
        
        $where="";
        $keyword="";
        $industry="";
        
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" Company.groupid = '".$user['groupid']."' and ";  
        		}     		  		
        	}
        }else{
        	 $where=" Company.groupid = '".$_GET['group']."' and ";
        }
        $group_mod_id=$_GET['group'];
        if(!empty($_GET['industry'])){
            $industry=$_GET['industry'];
            $objpidlist=$this->ProCvalue->findAllByCvid($industry);
            $pidlist="";
            if($objpidlist){
                foreach ($objpidlist as $pid){
                    $pidlist.="'".$pid["ProCvalue"]["pid"]."',";
                }
            }
            $wheres=rtrim($pidlist,",");	
            if(empty($wheres)){
                $wheres=1;
            }
            $where="Company.id in (".$wheres.") and ";
        }
        
        if(!empty($_GET['keyword'])){
            $keyword=$_GET['keyword'];
            $where.="(Company.company_name like '%".urldecode($keyword)."%') and ";
        }
        $where.="Company.is_deleted='0'";
        $this->Company->unbindModel(array ('hasMany' => array ('pros_cvalue','CompType','Attachment'),
        'belongsTo' => array ('Logo')));
        $urlcount=$this->Company->findCount($where);
        
        if(!empty($_GET['keyword']) || !empty($_GET['industry'])){
            $this->Session->setFlash('共检索出'.$urlcount.'条记录');
        }
        $paging['total'] =$urlcount;
        $showpage=0;
        $companys=array();
        
        $sort="desc";
        $capitalsort=$sort;
        $regsort=$sort;
        $columncapital="";
        if($sortBy=="capital"){
            $columncapital=$this->Codetype->getcapital().',';
            if($direction=="desc"){
                $capitalsort="asc";
            }
        }
        if($sortBy=="reg_time"){
            if($direction=="desc"){
                $regsort="asc";
            }
        }
        $this->set(compact('group_mod_id','capitalsort','regsort'));
        
        if ($paging['total'] > 0) {
            list ($order, $limit, $page) = $this->Pagination->init("", $paging);
            $this->Company->unbindModel(array ('hasMany' => array ('pros_cvalue','CompType','Attachment'),
            'belongsTo' => array ('Logo')));
            $order=$sortBy." ".$direction;
            if(empty($report)){
                $companys=$this->Company->findAll($where,"`Company`.`id`,`Company`.`groupid`, `Company`.`company_name`, `Company`.`reg_time`, `Company`.`capital`,$columncapital `Company`.`cap_type`, `Company`.`contact`, `Company`.`tel`",$order,$limit,$page);
            }else{
                $companys=$this->Company->findAll($where,"`Company`.`id`, `Company`.`groupid`,`Company`.`company_name`, `Company`.`reg_time`, `Company`.`capital`,$columncapital `Company`.`cap_type`, `Company`.`contact`, `Company`.`tel`");
            }
            if($urlcount>$pagesize)
            {
                $showpage=1;
            }
        }
        $this->set(compact('group_mod_id','companys','showpage','keyword','report','industry','sortBy','direction','page','limit','grouplist'));
        if($report){
            $this->layout="excel";
            $filename="企业统计情况总表.xls";
            $this->set(compact('filename'));
        }
    }
    function showcompinfo($id,$report=null)
    {
        $rate=$this->getrate();	 		 	
        $objcompany=array();			     		
        $objpatent=$this->Codetype->getCValues($this->Constant->PatentType);
        $invent="";
        $cinvent=$this->Constant->Invent;
        foreach ($objpatent as $key=>$patent){
            if($patent==$cinvent){
                $invent=$key;
            }
        }			
        $objdepart=$this->Codetype->getSubType($this->Constant->Nation);		
        $departidlist=array();
        foreach($objdepart as $key=>$depart){
            $departidlist[]=$key;
        }
        
         $year=date("Y");
         if(!empty($_GET['year'])){
            $year=$_GET['year'];
         }
         $this->CompReport->bindModel(
         array('hasMany' => array(
            'ProCvalue' => array (
            'fields'=>'pid',
            'className' => 'ProCvalue',
            'foreignKey' => 'pid',
            ),
            'CompType' => array (
            'fields'=>'cid',
            'className' => 'CompType',
            'foreignKey' => 'cid',        		
            ),
            'DevelopmentCost'=>array(
            'fields'=>'cid,c_time,cost,research_exp,new_product,govern_funding,raised_enterprise',
            'className'=>'DevelopmentCost',
            'conditions' => array("DevelopmentCost.is_deleted = '0' and year(DevelopmentCost.c_time)='$year'"),		
            'foreignKey'=>'cid'
            ),
            'Achievement'=>array(
            'fields'=>'cid,is_rely',
            'className'=>'Achievement',
            'conditions' => array("Achievement.is_deleted = '0' and year(Achievement.a_time)='$year'"),		
            'foreignKey'=>'cid'
            ),			
            'CompAchievement'=>array(
            'fields'=>'cid,output_achievement,award_achievement',
            'className'=>'CompAchievement',			
            'foreignKey'=>'cid'
            ),	
            'CompInstitution'=>array(
            'fields'=>'cid,institution_name',
            'className'=>'CompInstitution',
            'conditions' => array("CompInstitution.is_deleted = '0'"),		
            'foreignKey'=>'cid'
            ),			
            'CompEconomic'=>array(
            'fields'=>'cid,general_income,prospect,net_profit,over_taxes,qcll,company_year',
            'className'=>'CompEconomic',
            'conditions' => array("CompEconomic.is_deleted = '0' and CompEconomic.state = '1' and CompEconomic.company_year='$year'"),
            'foreignKey'=>'cid'
            ),			
            'CompanyPatent'=>array(
            'fields'=>'cid,type_id,state',
            'className'=>'CompanyPatent',
            'conditions' => array("CompanyPatent.is_deleted = '0' and year(CompanyPatent.created)='$year'"),
            'foreignKey'=>'cid'
            ),			
//			'CompTalent'=>array(
//			'className'=>'CompTalent',
//			'conditions' => array("CompTalent.is_deleted = '0'"),
//		    'foreignKey'=>'cid'
//			),
            'CompPerson'=>array(
            'className'=>'CompPerson',
            'conditions' => array("CompPerson.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),
            'CompTechnology'=>array(
            'fields'=>'cid,department',
            'className'=>'CompTechnology',
            'conditions' => array("CompTechnology.is_deleted = '0' and year(CompTechnology.created)='$year'"),
            'foreignKey'=>'cid')
            ))
        );
         
        if($id)
        {
            $this->loadModel("CompReport");
            $objcompany=$this->CompReport->findById($id);
            $this->data=$objcompany;
            $reg_time=$this->data["CompReport"]["reg_time"];
            if($reg_time){
                $this->data["CompReport"]["reg_time"]=date("Y-m-d",strtotime($reg_time));
            }
            $resident_time=$this->data["CompReport"]["resident_time"];
            if($resident_time){
                $this->data["CompReport"]["resident_time"]=date("Y-m-d",strtotime($resident_time));
            }
        }
        $this->set(compact('objcompany','id','invent','departidlist','year','rate'));
        if($report){
            $this->layout="word";
            $filename="常熟领军人才企业情况表.doc";
            $this->set(compact('filename','report'));
        }
    }
    //获取美元汇率
    function getrate(){
        $this->loadModel("Constant");
        $this->loadModel("Codetype");
        $objrates=$this->Codetype->getCValues($this->Constant->ExchangeEate,1);
        $rate=0;
        $i=0;
        foreach ($objrates as $rates){
            if($i==0){
                $rate=$rates;
            }
            $i++;
        }
        return ($rates+0);
    }
    //常熟国家大学科技园在孵企业统计情况
    function allcompany($report=null)
    {
        $rate=$this->getrate();	 	
        $objcompany=array();			     
        $grouplist=$this->Group->getGroupList();		
        $objpatent=$this->Codetype->getCValues($this->Constant->PatentType);
        $invent="";
        $cinvent=$this->Constant->Invent;
        foreach ($objpatent as $key=>$patent){
            if($patent==$cinvent){
                $invent=$key;
            }
        }			
        $objdepart=$this->Codetype->getSubType($this->Constant->Nation);		
        $departidlist=array();
        foreach($objdepart as $key=>$depart){
            $departidlist[]=$key;
        }
        $this->CompReport->bindModel(
        array('hasMany' => array(
            'DevelopmentCost'=>array(
            'fields'=>'cid,c_time,cost,research_exp,new_product,govern_funding,raised_enterprise',
            'className'=>'DevelopmentCost',
            'conditions' => array("DevelopmentCost.is_deleted = '0'"),		
            'foreignKey'=>'cid'
            ),
            'Achievement'=>array(
            'fields'=>'cid,is_rely',
            'className'=>'Achievement',
            'conditions' => array("Achievement.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),
            'CompAchievement'=>array(
            'fields'=>'cid,output_achievement,award_achievement',
            'className'=>'CompAchievement',			
            'foreignKey'=>'cid'
            ),
            'CompInstitution'=>array(
            'fields'=>'cid,institution_name',
            'className'=>'CompInstitution',
            'conditions' => array("CompInstitution.is_deleted = '0'"),		
            'foreignKey'=>'cid'
            ),	
            'CompEconomic'=>array(
            'fields'=>'cid,general_income,prospect,net_profit,over_taxes,qcll,company_year',
            'className'=>'CompEconomic',
            'conditions' => array("CompEconomic.state = '1' and CompEconomic.state = '1' and CompEconomic.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),			
            'CompanyPatent'=>array(
            'fields'=>'cid,type_id,state',
            'className'=>'CompanyPatent',
            'conditions' => array("CompanyPatent.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),			
//			'CompTalent'=>array(
//			'className'=>'CompTalent',
//			'conditions' => array("CompTalent.is_deleted = '0'"),
//		    'foreignKey'=>'cid'
//			),
            'CompPerson'=>array(
            'className'=>'CompPerson',
            'conditions' => array("CompPerson.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),
            'CompTechnology'=>array(
            'fields'=>'cid,department',
            'className'=>'CompTechnology',
            'conditions' => array("CompTechnology.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),
            'CompCarrierlease' => array (
            'className' => 'CompCarrierlease',
            'conditions' => array("is_deleted='0' and deadline >= now()"),
            'fields'=>"id,area",
            'foreignKey' => 'cid')
            ))
        );
         
        
        $this->loadModel("CompReport");
        $industrys=$this->Codetype->getCValues($this->Constant->Industry,"all");
        $this->set(compact('industrys'));
        $industry="";
        $wherec="";
        $user=$this->Session->read('User');
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $wherec=" CompReport.groupid = '".$user['groupid']."' and "; 
        		}      		  		
        	}
        }else{
        	
        	 $wherec=" CompReport.groupid = '".$_GET['group']."' and ";
        }
        $group_mod_id=$_GET['group'];
        if(!empty($_GET['industry'])){
            $industry=$_GET['industry'];
            $objpidlist=$this->ProCvalue->findAllByCvid($industry);
            $pidlist="";
            if($objpidlist){
                foreach ($objpidlist as $pid){
                    $pidlist.="'".$pid["ProCvalue"]["pid"]."',";
                }
            }
            $wheresc=rtrim($pidlist,",");	
            if(empty($wheresc)){
                $wheresc=1;
            }
            $wherec="CompReport.id in (".$wheresc.") and ";
        }
        $wherec.="CompReport.is_deleted='0'";
        $objcompanys=$this->CompReport->findAll($wherec);
        $this->set(compact('group_mod_id','objcompanys','invent','departidlist','industry','rate','grouplist'));
        if($report){
            $this->layout="word";
            $filename="常熟领军人才企业统计情况.doc";
            $this->set(compact('filename','report'));
        }
    }
    
    //产业饼图、柱状图
    function allindustry($report=null){
        $Company = classregistry::init('Company');
    	$user=$this->Session->read('User');
        if(empty($_GET['group'])){
        	if($user['isadmin']||$user['isvisitor']){  
        		$ind_comps = $this->Company->getIndComp(); 
        	}else{
        		$ind_comps = $this->Company->getIndCompByGroupId($user['groupid']); 
        	}
		}else{
		    $group=$_GET['group'];
       		$ind_comps = $this->Company->getIndCompByGroupId($group); 
			
		}
	
		$group_mod_id=$_GET['group'];
		$grouplist=$this->Group->getGroupList();
        $this->set(compact('group_mod_id','ind_comps'));
        if($report){
            $this->layout="word";
            $filename="产品情况分布.doc";
            $this->set(compact('filename','report'));
        }
        $this->set(compact('grouplist'));
        
    }
    //企业信息来源
    function compinfo($report=null){
        $Company = classregistry::init('Company');
    	$user=$this->Session->read('User');
        if(empty($_GET['group'])){
        	if($user['isadmin']||$user['isvisitor']){   	
        		$ind_comps = $this->Company->getInfoSourc(); 
        	}else{
        		$ind_comps = $this->Company->getInfoSourc($user['groupid']); 
        	}
		}else{
		    $group=$_GET['group'];
       		$ind_comps = $this->Company->getInfoSourc($group); 
			$group_mod_id=$_GET['group'];
			$this->set(compact('group_mod_id'));
		}
		
		$grouplist=$this->Group->getGroupList();
        $this->set(compact('ind_comps'));
        if($report){
            $this->layout="word";
            $filename="信息来源分布.doc";
            $this->set(compact('filename','report'));
        }
        $this->set(compact('grouplist'));
    }
    
    
    
//	function barchart(){
//		require_once("../webroot/jsbchartbchart/classesbchart.php"); 
////		ob_clean();
//		header("Content-type: image/png"); // 将此页面显示为一个图片
//		
//		$Company=classregistry::init('Company');
//		$ind_comps = $this->Company->getIndComp();
//		
//		$chart = new VerticalBarChart( 550 , 350 ) ; // 参数表示需要创建的图像的宽和高
//	  	$dataSet = new XYDataSet() ; // 实例化一个 XY 轴数据对象
//	  
//	  	foreach($ind_comps as $ind_comp){
//	  		// 为这个对象增加四组数据集合， Point 对象的第一个参数表示 X 轴坐标，第二个表示 Y 轴坐标
//	  		$industry = mb_convert_encoding($ind_comp["codevalues"]["value"], "html-entities","utf-8" );
//	  		$countcomp = $ind_comp[0]["comps"];
//	  		$sumcapital = $ind_comp[0]["capital"];
//	  		$sumsize = $ind_comp[0]["size"];
//	  		$dataSet->addPoint(new Point($industry,$countcomp,$sumcapital,$sumsize));
//	  	}
//	  	
//	    // 把这个数据集合传递给图形对象
//	    $chart->setDataSet($dataSet) ;
//	
//	    // 设置图形的标题，并把它作为一个 png 文件渲染
//	    $title = "常熟国家大学科技园产业情况分布柱状图";
//	    $title = mb_convert_encoding($title, "html-entities","utf-8" );
//	    $chart->setTitle($title) ;
//	    $chart->render() ;
//	}
//	function piechart(){
//		require_once("../webroot/jsbchartbchart/classesbchart.php"); 
////		ob_clean();
//		header("Content-type: image/png"); // 将此页面显示为一个图片 
//		$chart = new PieChart(550, 350);
//		
//		$Company=classregistry::init('Company');
//		$ind_comps = $this->Company->getIndComp();
// 
//		$dataSet = new XYDataSet();
//		foreach($ind_comps as $ind_comp){
//	  		// 为这个对象增加四组数据集合， Point 对象的第一个参数表示 X 轴坐标，第二个表示 Y 轴坐标
//	  		$industry = mb_convert_encoding($ind_comp["codevalues"]["value"], "html-entities","utf-8" );
//	  		$countcomp = $ind_comp[0]["comps"];
//	  		$sumcapital = $ind_comp[0]["capital"];
//	  		$sumsize = $ind_comp[0]["size"];
//	  		$dataSet->addPoint(new Point($industry,$countcomp,$sumcapital,$sumsize));
//	  	}
//		$chart->setDataSet($dataSet);
//		 
//		$title = "常熟国家大学科技园产业情况分布饼图";
//	    $title = mb_convert_encoding($title, "html-entities","utf-8" );
//	    $chart->setTitle($title) ;
//		$chart->render();
//	}
    
    //产业统计报表
    function totaldatareport($report = null){
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        //获取当前的年月
        $year = date('Y');
        $month = date('m');
        if($month < 10){
            $month = substr($month, 1);
        }
        if(!empty($_GET['year'])){
            $year = $_GET['year'];
        }
        if(!empty($_GET['month'])){
            $month = $_GET['month'];
        }
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" and  Company.groupid = '".$user['groupid']."'";  
        		}     		  		
        	}
        }else{
        	 $where=" and  Company.groupid = '".$_GET['group']."'";
        }        
        $group_mod_id=$_GET['group'];
        
        $totaldatas = classregistry::init('Company')->totaldataReport($year, $month,$where);
        $last_totaldatas = classregistry::init('Company')->totaldataReport($year-1, $month,$where);
        $months = $this->Constant->Month;
        
        $this->set(compact('group_mod_id',"totaldatas","last_totaldatas","months","year","month","grouplist"));
        if($report){
            $this->layout="excel";
            $filename=$year."年".$month."月入驻企业统计汇总报表.xls";
            $this->set(compact('filename','report'));
        }
    }
    
    //企业统计报表
    function compdatareport($report = null){
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();    	
        $forbids=$this->Codetype->getCValues($this->Constant->Forbid);
        $declaretype=$this->Talstack->getTname();
        $declare_status= $this->Constant->Declarate_Status;
        $successDeclare = $this->Constant->SuccessDeclare;
        $comptype = $this->Codetype->getCValues($this->Constant->Companytype);
        $ht_pro = $this->Codevalue->findByValue($this->Constant->HighTechProduct);
        $sw_pro = $this->Codevalue->findByValue($this->Constant->SoftwareProduct);
        $ht_cvid = $ht_pro["Codevalue"]["id"];
        $sw_cvid = $sw_pro["Codevalue"]["id"];
        //获取当前的年月
        $year = date('Y');
        $month = date('m');
        if($month < 10){
            $month = substr($month, 1);
        }
        if(!empty($_GET['year'])){
            $year = $_GET['year'];
        }
        if(!empty($_GET['month'])){
            $month = $_GET['month'];
        }
        $where='';
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" and  SearchCompany.groupid = '".$user['groupid']."'";
        		}       		  		
        	}
        }else{
        	 $where=" and SearchCompany.groupid = '".$_GET['group']."'";
        }        
        
        $months = $this->Constant->Month;
        $successDeclareId = $this->Codetype->findCodeValueID($declare_status,$successDeclare);
        $compdatas = classregistry::init('Company')->compdata($year, $month,$where);
        $last_compdatas = classregistry::init('Company')->compdata($year, $month,$where);
        $this->set(compact("compdatas","last_compdatas","months","year","month","comptype","ht_cvid","sw_cvid","forbids","successDeclareId","declaretype","grouplist"));
        if($report){
            $this->layout="excel";
            $filename=$year."年".$month."月入驻企业统计报表.xls";
            $this->set(compact('filename','report'));
        }
    }
    //企业产业化情况表
    function compindustryreport($report = null){
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $where='';
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where="companies.groupid = '".$user['groupid']."'";  
        		}     		  		
        	}
        }else{
        	 $where=" companies.groupid = '".$_GET['group']."'";
        	 $group_mod_id=$_GET['group'];
        	 $this->set(compact('group_mod_id'));
        }
     
        
    	$data = $this->CompEconomic->findRecentdataGroupByCid($where);
    	$this->set(compact('data','grouplist'));
        if($report){
            $this->layout="excel";
            $filename="企业产业化情况表.xls";
            $this->set(compact('filename','report'));
        }
    }

    /**
    *表名:高层次人才团队统计表
    *高层次人才:该表统计了企业信息中申报常熟领军人才项目（申报状态是已立项和未立项）的企业，报表按照人才项目的申请时间年度进行分组
    *人才项目情况：如果申报状态是已立项，则创业或者创新。如果申报状态是未立项，则显示未评上。只显示未立项或者已立项的人才项目
    *@author cyjiang
    */
    function talentdeclarereport($report = null)
    {
        $education_array = $this->Constant->Education;
        unset($education_array[1]);
        $this->Talstack->gettalentpname($this->Constant->LeadTalent);
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $father_name = $this->Constant->Declarate_Status;
        $type_name = $this->Constant->SuccessDeclare;
        $talent_delcare_success_id = $this->Codetype->findCodeValueID($father_name,$type_name);//常熟海内外领军人才申报已立项的ID
        $type_name = $this->Constant->FailDeclare;
        $talent_delcare_fail_id = $this->Codetype->findCodeValueID($father_name,$type_name);//常熟海内外领军人才申报未立项的ID
        $where='';
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" SearchCompany.groupid = '".$user['groupid']."' and ";
        		}       		  		
        	}
        }else{
        	 $where=" SearchCompany.groupid = '".$_GET['group']."' and ";
        }
        $group_mod_id=$_GET['group'];
        $idlist = $this->Talstack->getTalstackidlist();//获取常熟海内外领军人才id及其子类别的id
        $cidlist = $this->CompTalent->getCSLeadCompanyId(1);
        $where.= "SearchCompany.is_deleted = '0' and SearchCompany.id in ($cidlist)";
        $this->SearchCompany->bindModel(
            array('hasMany' => array(
                'CompTalent' => array (
                    'conditions' => array("CompTalent.is_deleted='0' and CompTalent.type_id in ($idlist) and CompTalent.declarate_status in ('$talent_delcare_success_id','$talent_delcare_fail_id')"),
                    'className' => 'CompTalent',
                    'fields'=>'CompTalent.declare_time,CompTalent.type_id,CompTalent.declarate_status',
                    'foreignKey' => 'cid'
                )
            ))
        );
        $companys = $this->SearchCompany->findAll($where,"`SearchCompany`.`company_name`,`SearchCompany`.`groupid`,`SearchCompany`.`pid`,`SearchCompany`.`contact`,`SearchCompany`.`sex`,`SearchCompany`.`birthday`,`SearchCompany`.`nation`,`SearchCompany`.`graduate_school`,`SearchCompany`.`education`,`SearchCompany`.`reg_time`,`SearchCompany`.`capital`,`SearchCompany`.`cap_type`");
        $this->set(compact('group_mod_id',"companys","education_array",'talent_delcare_fail_id','grouplist'));
        if($report){
            $this->layout="excel";
            $filename="常熟领军人才高层次人才团队统计报表.xls";
            $this->set(compact('filename','report'));
        }
    }

    //融资统计
    function finacreport($report = null)
    {
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();    	
        $education_array = $this->Constant->Education;
        unset($education_array[1]);
        $cidlist = $this->CompTalent->getCSLeadCompanyId(0);
        $where = "SearchCompany.is_deleted = '0' and SearchCompany.id in ($cidlist)";
        $this->SearchCompany->bindModel(
            array('hasMany' => array(
                'CompFinanc' => array (
                    'conditions' => array("CompFinanc.is_deleted='0'"),
                    'className' => 'CompFinanc',
                    'fields'=>'CompFinanc.money,CompFinanc.percent,CompFinanc.fin_time,CompFinanc.fin_channels,CompFinanc.type_id',
                    'foreignKey' => 'cid'
                )
            ))
        );
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where.=" and SearchCompany.groupid = '".$user['groupid']."'";
        		}       		  		
        	}
        }else{
        	 $where.=" and SearchCompany.groupid = '".$_GET['group']."'";
        }
        $group_mod_id=$_GET['group'];
        $companys = $this->SearchCompany->findAll($where,"`SearchCompany`.`company_name`,`SearchCompany`.`groupid`,`SearchCompany`.`contact`,`SearchCompany`.`nation`,`SearchCompany`.`education`,`SearchCompany`.`reg_time`,`SearchCompany`.`capital`,`SearchCompany`.`cap_type`");
        $this->set(compact('group_mod_id',"companys","education_array","grouplist"));
        if($report){
            $this->layout="excel";
            $filename="常熟领军人才入驻企业融资情况统计表.xls";
            $this->set(compact('filename','report'));
        }
    }
    
    //科技项目奖励汇总
    function technologyreport($report = null)
    {
    	$user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $status=$this->findSuccessDeclareId();
		$where="";
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" and companies.groupid = '".$user['groupid']."'"; 
        		}      		  		
        	}
        }else{
        	 $where=" and  companies.groupid = '".$_GET['group']."'";
        	  $group_mod_id=$_GET['group']; 
        }
             
        $ind_comps = $this->Company->gettech($status,$where); 
        $this->set(compact('group_mod_id','ind_comps','grouplist'));
        if($report){
            $this->layout="excel";
            $filename="科技项目资助情况统计表.xls";
            $this->set(compact('filename','report'));
        }
        
    }

    //返回申报状态中已立项的id
    function findSuccessDeclareId()
    {
        $this->loadModel("CodeType");
        $status = $this->Codetype->getCValues($this->Constant->Declarate_Status);
        $successStatue = $this->Constant->SuccessDeclare;
        foreach($status as $key=>$statue)
        {
            if($successStatue == $statue)
                return $key;
        }
        return "";
    }
    
    /**
    * 表名:	领军人才统计表
    * 该表统计了企业信息中申报成功常熟领军人才项目（已立项）的企业。
    * 备注：已立项的所有人才项目信息。人才项目信息格式：2011年姑苏人才创业B类，2012年国家领军人才。
    * @author cyjiang
    */
    function talentreport($report = null)
    {
    	$user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $education_array = $this->Constant->Education;
        unset($education_array[1]);
        $successid = $this->findSuccessDeclareId();
        $this->Talstack->gettalentpname($this->Constant->LeadTalent);
        $where='';
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" SearchCompany.groupid = '".$user['groupid']."' and ";  
        		}     		  		
        	}
        }else{
        	 $where=" SearchCompany.groupid = '".$_GET['group']."' and ";
        } 
        $group_mod_id=$_GET['group'];       
        $idlist = $this->Talstack->getTalstackidlist();
        $cidlist = $this->CompTalent->getCSLeadCompanyId(2);
        $where.= "SearchCompany.is_deleted = '0' and SearchCompany.id in ($cidlist)";
        $this->SearchCompany->bindModel(
            array('hasMany' => array(
                'CompTalent' => array (
                    'conditions' => array("CompTalent.is_deleted='0'"),
                    'className' => 'CompTalent',
                    'fields'=>'CompTalent.declare_time,CompTalent.type_id',
                    'foreignKey' => 'cid'
                )
            ))
        );
        $companys = $this->SearchCompany->findAll($where,"`SearchCompany`.`company_name`,`SearchCompany`.`groupid`,`SearchCompany`.`pid`,`SearchCompany`.`contact`,`SearchCompany`.`sex`,`SearchCompany`.`birthday`,`SearchCompany`.`nation`,`SearchCompany`.`graduate_school`,`SearchCompany`.`education`,`SearchCompany`.`reg_time`,`SearchCompany`.`municipal_capital`,`SearchCompany`.`municipal_cash`,`SearchCompany`.`custp_capital`,`SearchCompany`.`custp_cash`,`SearchCompany`.`capital`,`SearchCompany`.`cap_type`");
        $this->set(compact('group_mod_id',"companys","education_array","grouplist"));
        if($report){
            $this->layout="excel";
            $filename="领军人才统计报表.xls";
            $this->set(compact('filename','report'));
        }
    }
    
    /**
    * 表名:当年引进项目统计表
    * 此表按照注册时间统计了引进项目中的项目。
    * 备注：人才项目信息格式：2012年常熟市领军人才创业B类／创新A类。
    * @author cyjiang
    */
    function talentimportreport($report = null)
    {
        $year = date('Y');
        if(!empty($_GET['year'])){
            $year = $_GET['year'];
        }
        $education_array = $this->Constant->Education;
        unset($education_array[1]);
        $this->Talstack->gettalentpname($this->Constant->LeadTalent);
        $idlist = $this->Talstack->getTalstackidlist();//获取常熟海内外领军人才id及其子类别的id
        $this->Project->unbindModel(array ('hasMany' => array ('Attachment','pros_cvalue','Informationfrom','Competence'),'belongsTo' => array ('phase_cvalue','reg_cvalue','reg_decl_cvalue')));
        $where = "Project.is_deleted = '0' and Project.reg_time like '".$year."%'";
        $projects = $this->Project->findAll($where);
        $this->set(compact("projects","education_array","year","idlist"));
        if($report){
            $this->layout="excel";
            $filename=$year."年引进项目统计表.xls";
            $this->set(compact('filename','report'));
        }
    }
    
    //人才项目奖励
    function talentrewardreport($report = null){
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $successid = $this->findSuccessDeclareId();
        $where="";
       	if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where="  and Company.groupid = '".$user['groupid']."'";  
        		}     		  		
        	}
        }else{
        	 $where=" and  Company.groupid = '".$_GET['group']."'";
        }  
        $group_mod_id=$_GET['group'];      
        $objtalent=$this->CompTalent->getTalent($successid);
        $objtalstack=$this->Talstack->findAll("super_class is null and is_deleted='0'","Talstack.id,Talstack.tname");
        $talentlist=array();
        foreach ($objtalstack as $talstack){
            $id=$talstack['Talstack']['id'];
            $tname=$talstack['Talstack']['tname'];
            $talentlist[$id]=$tname;
        }
        $objreward=array();
        $i=0;
        foreach ($objtalent as $talent){
            $tname=$talentlist[$talent];
            $objreward[$i]["id"]=($i+1);
            $objreward[$i]["tname"]=$tname;
            $this->Talstack->talstackidlist="";
            $this->Talstack->gettalentpname($tname);
            $tid=$this->Talstack->getTalstackidlist();
            $this->CompTalent->unbindModel(array ('hasMany' => array ('Attachment'),'belongsTo' => array ('Talstack')));
            $this->CompTalent->bindModel(
                array("belongsTo" => array(
                    'SearchCompany'=>array(
                        'conditions' => array("SearchCompany.is_deleted='0'"),
                        'fields'=>'SearchCompany.contact,SearchCompany.company_name',
                        'className' => 'SearchCompany',
                        'foreignKey' => 'cid',
                    )
                ))
            );     
            $objtalentlist=$this->CompTalent->findAll("CompTalent.declarate_status='$successid' and CompTalent.type_id<>'' and CompTalent.type_id in ($tid) and CompTalent.is_deleted='0'".$where,"CompTalent.award,CompTalent.project_time,SearchCompany.contact,SearchCompany.company_name,SearchCompany.groupid");
            $objreward[$i]["talent"]=$objtalentlist;
            $i++;
        }
        $this->set(compact('group_mod_id',"objreward","grouplist"));
        if($report){
            $this->layout="excel";
            $filename="人才项目资助情况统计表.xls";
            $this->set(compact('filename','report'));
        }
    }
    //常熟国家大学科技园企业专利情况汇总
    function patentsummary($report = null){
    	
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();    	
        $patenttype=$this->Codetype->getCValues($this->Constant->PatentType,1);
        //获取当前的年月
        $year = date('Y');
        $month = date('m');
        if($month < 10){
            $month = substr($month, 1);
        }
        if(!empty($_GET['year'])){
            $year = $_GET['year'];
        }
        if(!empty($_GET['month'])){
            $month = $_GET['month'];
        }
        $months = $this->Constant->Month;
        
        $where = "";
        $sql = "";
        $this->CompanyPatent->unbindModel(array ('hasMany' => array ('Attachment')));
        $objcids = $this->CompanyPatent->findAll("$sql CompanyPatent.is_deleted='0' and CompanyPatent.state='0' or CompanyPatent.state='2'","CompanyPatent.cid");
        $cidlist="";
        foreach ($objcids as $cid){
            $cidlist.="'".$cid["CompanyPatent"]["cid"]."',";
        }
        if($cidlist){
            $where.="SearchCompany.id in (".rtrim($cidlist,",").") and ";
        }else{
            $where.="SearchCompany.id in (1) and ";
        }
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where.=" SearchCompany.groupid = '".$user['groupid']."' and ";     
        		}  		  		
        	}
        }else{
        	 $where.=" SearchCompany.groupid = '".$_GET['group']."' and ";
        }
        $group_mod_id=$_GET['group'];
        $where .="SearchCompany.is_deleted='0'";
        $companys=array();
        $this->SearchCompany->bindModel(
            array('hasMany' => array(
            'CompanyPatent' => array (
            'conditions' => array(" $sql is_deleted='0'"),
            'fields'=>"`CompanyPatent`.`id`,`CompanyPatent`.`type_id`,`CompanyPatent`.`state`,`CompanyPatent`.`apply_time`,
                        `CompanyPatent`.`accept_time`,`CompanyPatent`.`authorize_time`",
            'className' => 'CompanyPatent',
            'foreignKey' => 'cid',)
            ))
        );
        $companys=$this->SearchCompany->findAll($where,"SearchCompany.id,SearchCompany.groupid,SearchCompany.pid,SearchCompany.company_name","SearchCompany.modified desc");
        $this->set(compact('group_mod_id','companys',"months","year","month","patenttype","pop_type","grouplist"));
        if($report){
            $this->layout="excel";
            $filename= $year."年".$month."月入驻企业专利情况统计表.xls";
            $this->set(compact('filename','report'));
        }
    }
    
//	//传入code类型的名字获取code类型的id
//	function findCodeValueID($father_name,$type_name)
//	{
//		$types = $this->Codetype->getCValues($father_name);
//		foreach($types as $key=>$type)
//		{
//			if($type_name == $type)
//				return $key;
//		}
//		return "";
//	}
    
    //科创园软件著作权统计
    function softwarecopyrightreport($report = null)
    {
        $user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
    	$father_name = $this->Constant->PatentType;
        $type_name = $this->Constant->SoftwareCopyright;
        $softwareCopyrightId = $this->Codetype->findCodeValueID($father_name,$type_name);	//获取软件著作权的id
        $where = "SearchCompany.is_deleted = '0'";
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where.=" and  SearchCompany.groupid = '".$user['groupid']."'"; 
        		}      		  		
        	}
        }else{
        	 $where.=" and  SearchCompany.groupid = '".$_GET['group']."'";
        }
        $group_mod_id=$_GET['group'];
        $this->SearchCompany->bindModel(
            array('hasMany' => array(
                'CompanyPatent' => array (
                'conditions' => array("CompanyPatent.is_deleted='0' and CompanyPatent.type_id = '$softwareCopyrightId' and CompanyPatent.state in ('2','3')"),
                'fields'=>"`CompanyPatent`.`pname`,`CompanyPatent`.`authorize_time`,`CompanyPatent`.`buy_time`,`CompanyPatent`.`state`",
                'className' => 'CompanyPatent',
                'foreignKey' => 'cid')
            ))
        );
        $companys = $this->SearchCompany->findAll($where,"SearchCompany.company_name,SearchCompany.groupid");
        $this->set(compact('group_mod_id',"companys","grouplist"));
        if($report){
            $this->layout="excel";
            $filename="软件著作权统计报表.xls";
            $this->set(compact('filename','report'));
        }
    }
            
    //科技申报情况立项类型    汇总报表
    function protypereport($pro_type,$report=null){
    	$user=$this->Session->read('User');
        $grouplist=$this->Group->getGroupList();
        $types=$this->Codetype->getTechnologyType($this->Constant->TechnologyReport);
        $type_name = $types[$pro_type];
        $successid = $this->findSuccessDeclareId();
        $where = "";
        $sql = "CompTechnology.declarate_status='".$successid."' and CompTechnology.type_id = '".$pro_type."' and";
        $this->CompTechnology->unbindModel(array ('hasMany' => array ('Attachment')));
        $objcids = $this->CompTechnology->findAll("$sql CompTechnology.is_deleted='0'","CompTechnology.cid");
        $cidlist="";
        foreach ($objcids as $cid){
            $cidlist.="'".$cid["CompTechnology"]["cid"]."',";
        }
        if($cidlist){
            $where.="SearchCompany.id in (".rtrim($cidlist,",").") and ";
        }else{
            $where.="SearchCompany.id in (1) and ";
        }
        if(empty($_GET['group'])){
        	if(!$user['isadmin']){
        		if(!$user['isvisitor']){
				 $where=" SearchCompany.groupid = '".$user['groupid']."' and ";    
        		}   		  		
        	}
        }else{
        	 $where=" SearchCompany.groupid = '".$_GET['group']."' and ";
        }
        $group_mod_id=$_GET['group'];
        $where .="SearchCompany.is_deleted='0'";
        $companys=array();
        $this->SearchCompany->bindModel(
            array('hasMany' => array(
            'CompTechnology' => array (
            'conditions' => array(" $sql is_deleted='0'"),
            'fields'=>"`CompTechnology`.`id`,`CompTechnology`.`tname`,`CompTechnology`.`authorize_time`",
            'className' => 'CompTechnology',
            'foreignKey' => 'cid',)
            ))
        );
        $companys=$this->SearchCompany->findAll($where,"SearchCompany.id,SearchCompany.groupid,SearchCompany.pid,SearchCompany.company_name","SearchCompany.modified desc");
        $this->set(compact('group_mod_id','companys','pro_type','type_name','grouplist'));
        if($report){
            $this->layout="excel";
            $filename="常熟领军人才".$type_name."统计报表.xls";
            $this->set(compact('filename','report'));
        }
    }
    //打开页面前执行的方法
    function beforeFilter()
    {
        parent::beforeFilter();
        $user=$this->Session->read('User');
        if (!$user){
            $this->redirect('/users/login');
        }
    }
    //打开页面后执行的方法
    function afterFilter()
    {
        
    }
    //转化成word文档
    function toword(){
    	$this->layout="toword";	
    	$Company = classregistry::init('Company');
    	$user=$this->Session->read('User');
        if(empty($_GET['group'])){
        	if($user['isadmin']||$user['isvisitor']){   	
        		$ind_comps = $this->Company->getIndComp(); 
        	}else{
        		$ind_comps = $this->Company->getIndCompByGroupId($user['groupid']); 
        	}
		}else{
		    $group=$_GET['group'];
       		$ind_comps = $this->Company->getIndCompByGroupId($group); 
			
		}
		$group_mod_id=$_GET['group'];
		$grouplist=$this->Group->getGroupList();
        $this->set(compact('group_mod_id','ind_comps','grouplist'));
        
    }
    
}
?>
